Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Design Considerations

By looking at the data access patterns for your system, you should have a good idea of how the system operates. Before looking at the design of the system, here is a review of a few concepts introduced in earlier chapters:

  I/Os are typically the limiting factor in the system. You can only do a fixed number of random I/Os per second per disk drive (refer to Chapter 14, “Advanced Disk I/O Concepts”).
  I/Os can be reduced by caching data blocks in the SGA. If the data you want to access is already in the SGA, a disk I/O is not required.
  Isolate sequential I/Os. Most of the time spent reading from or writing to the disk is spent seeking to where the data is located. If you can reduce seeks, you can achieve more I/Os per second.
  Spread out random I/Os. Random I/Os have a maximum rate per drive. By spreading the I/Os out among many drives, you increase the overall rate.
  Avoid paging and swapping. Any time the system pages or swaps, performance is severely degraded. Avoid this at all costs.

All these factors contribute to the optimal data layout of the system. The physical layout—along with SGA and shared pool tuning—creates an optimally configured server for decision support tasks. In decision support systems, the design of the queries is also very important, as you will see in Part IV of this book, “Tuning SQL.”

Physical Data Layout

This section looks at how the data on a DSS system should be configured. First, it looks at how to lay out the data on traditional disks; then it looks at disk arrays. I recommend using disk arrays if at all possible; the ease of use and performance benefits are worth the cost of the array.

The main goal in designing the physical data layout is to balance the I/O across all the disks that are randomly accessed and to isolate the sequential I/O. Because the DSS system does not log very much, there isn’t much sequential I/O to worry about. Because there is an insignificant amount of updating, the redo log files and archive logs do not see much use. You also know that the majority (if not all) of the data files are accessed in a random fashion but can take advantage of multiblock reads.

Many of the concepts presented here represent the best-case scenario. Budgetary constraints may not allow everyone to buy the optimal number of disks for their DSS configuration. Make the best of these guidelines. Remember that it is the number of disks that provide performance with random I/Os; if you have the opportunity to buy one 4 gigabyte disk drive or two 2 gigabyte disk drives, the best performance comes from the two 2 gigabyte disk drives.

Traditional Disks

The layout for a typical DSS system is fairly straightforward. The minimal configuration should look something like this:


Element (# of Disks) Comments

System (1+) The system disk is used for the operating system, swap file (if applicable), user files, and Oracle binaries.
Redo log (0) Because very little update activity is involved with decision support activities, the log files are not active and are not a bottleneck to the system. These log-file drives can be better used for data files. The redo log files can reside on the same disk as the system.
Redo log (2+) If online updates are done to the system, you must separate the redo log files, both for protection and performance. Make your decision based on the extent of the updates you plan to do.
Archive logs (0) As with the redo log files, archiving is minimal and not necessary for a DSS system. The archive log files can also reside on the system disk.
Archive logs (1+) As with the redo log files, if updates are done on this system, you must provide for the sequential nature of the archive log files.
Data files (1+) The number of disks you need for data is determined by the amount of random I/O your user community generates.
Index files (1+) The number of disks needed for indexes is determined by the size of the indexes and the number of I/Os to the indexes.

Both the data files and the indexes should be striped over as many disk drives as necessary to achieve optimal I/O rates on those disks. From Chapter 14, “Advanced Disk I/O Concepts,” remember that you can only push a disk drive to a maximum random I/O rate.

As you have seen in previous chapters, the data and indexes can be striped across the disks using Oracle or RAID striping or a combination of the two. With OLTP and batch processing systems, I recommended OS or hardware striping (as is the case here). But unlike OLTP or batch processing systems, in order to accommodate the Oracle Parallel Query option, it is important that queries are more optimally divided if you have several large extents. If you do not use Oracle striping and build one large extent, you may not see the full benefits of the Parallel Query option. So I recommend using OS or hardware striping, but divide your tablespace into multiple data files, each with several large extents, to accommodate parallel query processing.

I prefer a hardware disk array to manual Oracle striping primarily because the disk array provides excellent performance and is easy to use. When you use a disk array, the task of distributing I/Os can be greatly simplified.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.